# -*- encoding: utf-8 -*-
from pyspider.libs.utils import md5string
import time, re, uuid, gprof2dot
import MySQLdb
import sys, random

cnx = MySQLdb.connect(host='192.168.1.5', user='root', passwd='w111111', db='RWLS', port=3306, charset='utf8')
cursor = cnx.cursor()

sql = "select * from user limit 0, 100"
# 创建存储过程
# sql = """
#   create procedure lckw_set_power(
# 		IN lc_date datetime
#         , IN lId VARCHAR (18)
#         , IN p DOUBLE (10,4))
#
#     BEGIN
# 		set @var = (SELECT p.power
# 					FROM (select lcId, power from power where createDate = lc_date) as p
#                     WHERE p.lcId = lId);
#
#         REPLACE INTO power (lcId, power, createDate)
#         VALUES (lId
# 			  , CASE WHEN @var is null
# 					THEN p
# 					ELSE @var + p
# 					END
# 			  , lc_date);
#     END
# """
cursor.execute(sql)
cnx.commit()
cursor.fetchall()




# def idBuild(head, num, now):
#     zero = ''
#     for z in range(num - len(str(now))):
#         zero = zero + '0'
#     finalId = head + zero + str(now)
#     return finalId
#
#
# roadSql = "insert into road values ('%s', '%.7f', '%.7f', CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())"
# sectionSql = "insert into section values ('%s', '%s', '%.7f', '%.7f', CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())"
# baseSql = "insert into baseStation values ('%s', '%s', '%s', NULL, '%.7f', '%.7f', '%s', CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())"
# lampSql = "insert into lampClient (lcId, baId, sectionId, roadId, whiteList, N, E, state, updateDate, createDate) values "
# model = "('%s', '%s', '%s', '%s', 0, '%.7f', '%.7f', '%s', CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())"
# lampsCost = 0
# baseCost = 0
# sectionCost = 0
#
# # 街道生成
# for rNum in range(30):
#     roadId = idBuild("RD", 6, rNum)
#     # print roadId
#     cursor.execute(roadSql % (roadId, random.uniform(0, 180), random.uniform(0, 180)))
#
#     # 路段生成
#     thisRoadSections = random.randint(2, 8)
#     for sNum in range(thisRoadSections):
#         sectionNumber = sNum + sectionCost
#         sectionId = idBuild("SE", 6, sectionNumber)
#         # print sectionId
#         cursor.execute(sectionSql % (sectionId, roadId, random.uniform(0, 180), random.uniform(0, 180)))
#
#         # 基站生成
#         thisSectionBases = random.randint(5, 25)
#         for bNum in range(thisSectionBases):
#             baseNumber = bNum + baseCost
#             baseId = idBuild("BS", 6, baseNumber)
#             # print baseId
#             cursor.execute(baseSql % (baseId, sectionId, roadId, random.uniform(0, 180), random.uniform(0, 180), random.randint(0, 3)))
#             manyInsert = ''
#
#             # 灯端生成
#             thisBaseLamps = random.randint(60, 200)
#             for lNum in range(thisBaseLamps):
#                 lampNumber = lNum + lampsCost
#                 lampId = idBuild('', 8, lampNumber)
#                 # print lampId
#                 datas = (lampId, baseId, sectionId, roadId, random.uniform(0, 180), random.uniform(0, 180), random.randint(0, 3))
#                 if lNum == thisBaseLamps-1:
#                     manyInsert = manyInsert + (model % datas)
#                 else:
#                     manyInsert = manyInsert + (model % datas) + ","
#
#             # print manyInsert
#             lampsCost = lampsCost + thisBaseLamps
#             insertlampSql = lampSql + manyInsert
#             cursor.execute(insertlampSql)
#             cnx.commit()
#
#         baseCost = baseCost + thisSectionBases
#
#     sectionCost = sectionCost + thisRoadSections
